package com.huaxia.dao.performance;

import com.huaxia.pojo.performance.SaleDeptReach;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/05/20 11:23
 */
public interface SaleDeptReachMapper {

    @Select("select a.SALEDEPTSNAME,round(a.increaseMonth/10000,0)deptMonthIncrease,round(j.deptQuarterInSure/10000,0)deptQuarterInSure,round((j.deptQuarterInSure-qj.deptQuarterInSure)/10000,0)deptQuarterIncrease,round(n.deptYearInSure/10000,0)deptYearInSure,round((n.deptYearInSure-qn.deptYearInSure)/10000,0)deptYearIncrease from\n" +
            "(select a.saledeptsname,a.ycb-b.ycb increaseMonth FROM\n" +
            "(select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt ycb from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.monthYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME)a, \n" +
            "(select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt ycb from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND  TO_CHAR(ISSUE_DATE,'MM')=TO_CHAR(SYSDATE,'MM') AND RNFLAG='N' AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.monthYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND  TO_CHAR(ISSUE_DATE,'MM')=TO_CHAR(SYSDATE,'MM')  AND RNFLAG='N' AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME)b where a.saledeptsname=b.saledeptsname\n" +
            ")a,\n" +
            "(select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt deptQuarterInSure from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.quarterInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)quarterInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND RNFLAG='N'AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.quarterYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)quarterYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND DEPTCODE1 IS NOT NULL  AND RNFLAG='N' GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME\n" +
            ")j,\n" +
            "(select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt deptQuarterInSure from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.quarterInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)quarterInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND RNFLAG='N'AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.quarterYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)quarterYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND DEPTCODE1 IS NOT NULL  AND RNFLAG='N' GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME\n" +
            ")qj,\n" +
            "( select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt deptYearInSure from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.yearInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day where  TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND RNFLAG='N'AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.yearYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day where  TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND DEPTCODE1 IS NOT NULL  AND RNFLAG='N' GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME\n" +
            ")n,\n" +
            "( select bcb.SALEDEPTSNAME,bcb.zcb-byt.zyt deptYearInSure from \n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(cb.yearInSure,0) zcb from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day where  TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND RNFLAG='N'AND DEPTCODE1 IS NOT NULL GROUP BY SALEDEPTCODE)cb RIGHT JOIN\n" +
            "SD_SALEDEPT ON cb.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)bcb left join\n" +
            "(select SD_SALEDEPT.SALEDEPTSNAME,NVL(yt.yearYt,0) zyt from\n" +
            "(select SALEDEPTCODE, SUM(WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day where  TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND DEPTCODE1 IS NOT NULL  AND RNFLAG='N' GROUP BY SALEDEPTCODE)yt right join \n" +
            "SD_SALEDEPT ON yt.SALEDEPTCODE=SD_SALEDEPT.SALEDEPTCODE)byt\n" +
            "on bcb.SALEDEPTSNAME=byt.SALEDEPTSNAME)qn where a.SALEDEPTSNAME=j.SALEDEPTSNAME and j.SALEDEPTSNAME=qj.SALEDEPTSNAME and qj.SALEDEPTSNAME=n.SALEDEPTSNAME and n.SALEDEPTSNAME=qn.SALEDEPTSNAME order by n.deptYearInSure desc\n" +
            "\n")
    List<SaleDeptReach> getSaleDeptReach();
}
